﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

using VK.Comm;
using VK.Comm.TransactSql;

namespace VK.Casemeet.Areas
{
    public static class AreaDAL
    {
        internal static AreaEntity GetAreaByReader(IDataReader dr)
        {
            AreaEntity area = null;

            area = new AreaEntity();
            area.AreaID = int.Parse(dr[0].ToString());
            area.Name = dr[1].ToString();
            area.ParentID = int.Parse(dr[2].ToString());
            area.Level = int.Parse(dr[3].ToString());
            area.HasChild = bool.Parse(dr[4].ToString());
            area.Sort = int.Parse(dr[5].ToString());

            return area;
        }

        internal static IList<AreaEntity> GetAreaList()
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<AreaEntity> arealst = new List<AreaEntity>();

            string SelectArea = @"
                SELECT [AreaID],[Name],[ParentID],[Level],[HasChild],[Sort] FROM [Area] ORDER BY Sort";
            DbCommand SelectAreaCommand = db.GetSqlStringCommand(SelectArea);

            using (IDataReader dr = db.ExecuteReader(SelectAreaCommand))
            {
                while (dr.Read())
                {
                    AreaEntity area = GetAreaByReader(dr);
                    arealst.Add(area);
                }
            }

            return arealst;
        }

        internal static IList<AreaEntity> GetAreaList(int parentID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<AreaEntity> arealst = new List<AreaEntity>();

            string SelectArea = @"
                SELECT [AreaID],[Name],[ParentID],[Level],[HasChild],[Sort] FROM [Area] WHERE [ParentID]=@ParentID ORDER BY Sort";
            DbCommand SelectAreaCommand = db.GetSqlStringCommand(SelectArea);
            db.AddInParameter(SelectAreaCommand, "@ParentID", DbType.Int32, parentID);

            using (IDataReader dr = db.ExecuteReader(SelectAreaCommand))
            {
                while (dr.Read())
                {
                    AreaEntity area = GetAreaByReader(dr);
                    arealst.Add(area);
                }
            }

            return arealst;
        }

        internal static AreaEntity GetArea(int areaID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            AreaEntity area = new AreaEntity();

            string SelectArea = @"
                SELECT [AreaID],[Name],[ParentID],[Level],[HasChild],[Sort] FROM [Area] WHERE [AreaID]=@AreaID";
            DbCommand SelectAreaCommand = db.GetSqlStringCommand(SelectArea);
            db.AddInParameter(SelectAreaCommand, "@AreaID", DbType.Int32, areaID);

            using (IDataReader dr = db.ExecuteReader(SelectAreaCommand))
            {
                if (dr.Read())
                {
                    area = GetAreaByReader(dr);
                }
            }

            return area;
        }

        internal static string GetAreaName(int areaID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string SelectArea = @"
                SELECT [Name] FROM [Area] WHERE [AreaID]=@AreaID";
            DbCommand SelectAreaCommand = db.GetSqlStringCommand(SelectArea);
            db.AddInParameter(SelectAreaCommand, "@AreaID", DbType.Int32, areaID);
            object obj = db.ExecuteScalar(SelectAreaCommand);
            return obj == null ? null : obj.ToString();
        }

        internal static int InsertArea(AreaEntity area)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string InsertArea = @"
                DECLARE @sortMAX AS INT, @OldLevel AS INT;
                SELECT @sortMAX=MAX(Sort) FROM [Area] WHERE ParentID=@ParentID;
                IF (@ParentID<>0)
                BEGIN
                    SELECT @OldLevel=[Level] FROM [Area] WHERE [AreaID]=@ParentID;
                END
		        ELSE
		        BEGIN
                    SET @OldLevel=1;
                END
                INSERT INTO [Area]
               ([Name],[ParentID],[Level],[HasChild],[Sort])
                VALUES
               (@Name,@ParentID,@OldLevel+1,0,ISNULL(@sortMAX,0)+1);
                UPDATE [Area] SET [HasChild]=1 WHERE [AreaID]=@ParentID;";

            DbCommand InsertAreaCommand = db.GetSqlStringCommand(WrapSql.WrapTransaction("Tran_InsertArea", InsertArea));
            db.AddInParameter(InsertAreaCommand, "@Name", DbType.String, area.Name);
            db.AddInParameter(InsertAreaCommand, "@ParentID", DbType.Int32, area.ParentID);
            result = db.ExecuteNonQuery(InsertAreaCommand);

            return result;
        }

        internal static int UpdateArea(AreaEntity area)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string UpdateArea = @"
            DECLARE @LevelNumber INT,@OldLevel INT,@NewLevel INT,@ParentChildCount INT; 
            SELECT @OldLevel=[Level] FROM Area WHERE AreaID=@AreaID; 
            SELECT @NewLevel=[Level]+1 FROM Area WHERE AreaID=@ParentID; 
            SET @LevelNumber=@NewLevel-@OldLevel; 
            UPDATE Area SET [Level] = pc.[Level] +(@LevelNumber) 
            FROM Area AS pc INNER JOIN 
                dbo.F_GetChildArea(@AreaID) AS fc ON pc.AreaID = fc.AreaID; 
            UPDATE [Area] 
            SET [Name]=@Name,[ParentID]=@ParentID,[Level]=@NewLevel
            WHERE [AreaID]=@AreaID; 
            SELECT @ParentChildCount=COUNT([AreaID]) FROM Area WHERE [ParentID]=@OldParentID;
            IF (@ParentChildCount>=1)
            BEGIN
                UPDATE [Area] SET [HasChild]=1 WHERE [AreaID]=@OldParentID; 
            END
		    ELSE
		    BEGIN
                UPDATE [Area] SET [HasChild]=0 WHERE [AreaID]=@OldParentID; 
            END 
            UPDATE [Area] SET [HasChild]=1 WHERE [AreaID]=@ParentID; ";

            DbCommand UpdateAreaCommand = db.GetSqlStringCommand(UpdateArea);
            db.AddInParameter(UpdateAreaCommand, "@AreaID", DbType.Int32, area.AreaID);
            db.AddInParameter(UpdateAreaCommand, "@Name", DbType.String, area.Name);
            db.AddInParameter(UpdateAreaCommand, "@ParentID", DbType.Int32, area.ParentID);
            db.AddInParameter(UpdateAreaCommand, "@OldParentID", DbType.Int32, area.OldParentID);
            result = db.ExecuteNonQuery(UpdateAreaCommand);
            
            return result;
        }

        /// <summary>
        /// DeleteArea
        /// </summary>
        /// <param name="productAreaID">productAreaID</param>
        /// <returns>0：失败。1：删除成功。2：分类存在子类不能被删除，请先删除所有子类。</returns>
        internal static int DeleteArea(int areaID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string DeleteArea = @"
            /* 0：失败 1：删除成功。2：分类存在子类不能被删除，请先删除所有子类。 */
	        Declare @ChildCount int, @RESULT AS INT
            SET @RESULT=0;

            BEGIN TRANSACTION Delete_Area;

            SELECT @ChildCount=COUNT(AreaID) FROM Area WHERE ParentID = @AreaID;

            IF (@ChildCount=0)
            BEGIN
                DELETE FROM Area WHERE [AreaID] = @AreaID
                SET @RESULT=1 
            END
            ELSE
            BEGIN
                IF (@ChildCount>0)
                BEGIN
                SET @RESULT=2 
                END
            END

            IF @@ERROR=0 
                BEGIN
                COMMIT TRANSACTION Delete_Area;
                END
            ELSE
                BEGIN
                ROLLBACK TRANSACTION Delete_Area;
                END

            SELECT @RESULT;";

            DbCommand DeleteAreaCommand = db.GetSqlStringCommand(DeleteArea);
            db.AddInParameter(DeleteAreaCommand, "@AreaID", DbType.Int32, areaID);
            object obj = db.ExecuteScalar(DeleteAreaCommand);
            result = obj == null ? 0 : int.Parse(obj.ToString());

            return result;
        }

        /// <summary>
        /// 向上移一位
        /// </summary>
        /// <param name="categoryID"></param>
        /// <returns>1：成功  2：已经在第一位无需移动</returns>
        internal static int SetAreaMoveUp(int areaID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string SetAreaMoveUp = @"
            /*   1：成功  2：已经在第一位无需移动   */
	        DECLARE @sort AS INT, @preSort AS INT, @preAreaID AS INT, @parentID AS INT, @minSort AS INT
	        DECLARE @RESULT AS INT; SET @RESULT=0;
        	
	        BEGIN TRANSACTION T_Area_MoveUp
        	
	        SELECT @sort=Sort, @parentID=ParentID FROM Area WHERE AreaID=@AreaID
	        SELECT @minSort=MIN(Sort) FROM Area WHERE ParentID=@parentID
	        IF @sort<>@minSort
	        BEGIN
		        SELECT TOP 1 @preAreaID=AreaID, @preSort=Sort FROM Area WHERE ParentID=@parentID AND Sort<@sort ORDER BY Sort DESC
		        UPDATE Area SET Sort=@preSort WHERE AreaID=@AreaID
		        UPDATE Area SET Sort=@sort WHERE AreaID=@preAreaID
		        SET @RESULT=1 
	        END
	        ELSE
	        BEGIN
		        SET @RESULT=2
	        END
        	
	        IF @@ERROR=0 
		        BEGIN
		        COMMIT TRANSACTION T_Area_MoveUp
		        END
	        ELSE
		        BEGIN
		        ROLLBACK TRANSACTION T_Area_MoveUp
		        END
        		
	        SELECT @RESULT";

            DbCommand InsertAreaCommand = db.GetSqlStringCommand(SetAreaMoveUp);
            db.AddInParameter(InsertAreaCommand, "@AreaID", DbType.Int32, areaID);
            object obj = db.ExecuteScalar(InsertAreaCommand);
            result = obj == null ? 0 : int.Parse(obj.ToString());

            return result;
        }

        /// <summary>
        /// 向下移一位
        /// </summary>
        /// <param name="categoryID"></param>
        /// <returns>1：成功  2：已经在最后无需移动</returns>
        internal static int SetAreaMoveDown(int areaID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string SetAreaMoveDown = @"
            /*   1：成功  2：已经在最后无需移动   */
	        DECLARE @sort AS INT, @nextSort AS INT, @nextAreaID AS INT, @parentID AS INT, @maxSort AS INT
	        DECLARE @RESULT AS INT; SET @RESULT=0;
        	
	        BEGIN TRANSACTION T_Area_MoveDown
        	
	        SELECT @sort=Sort, @parentID=ParentID FROM Area WHERE AreaID=@AreaID  --查出本节点编号，父节点编号
	        SELECT @maxSort=MAX(Sort) FROM Area WHERE ParentID=@parentID              --查出本节点内最大编号
	        IF @sort<>@maxSort
	        BEGIN
		        SELECT TOP 1 @nextAreaID=AreaID, @nextSort=Sort FROM Area WHERE ParentID=@parentID AND Sort>@sort ORDER BY Sort
		        UPDATE Area SET Sort=@nextSort WHERE AreaID=@AreaID
		        UPDATE Area SET Sort=@sort WHERE AreaID=@nextAreaID
		        SET @RESULT=1 
	        END
	        ELSE
	        BEGIN
		        SET @RESULT=2
	        END
        	
	        IF @@ERROR=0 
		        BEGIN
		        COMMIT TRANSACTION T_Area_MoveDown
		        END
	        ELSE
		        BEGIN
		        ROLLBACK TRANSACTION T_Area_MoveDown
		        END
        		
	        SELECT @RESULT";

            DbCommand InsertAreaCommand = db.GetSqlStringCommand(SetAreaMoveDown);
            db.AddInParameter(InsertAreaCommand, "@AreaID", DbType.Int32, areaID);
            object obj = db.ExecuteScalar(InsertAreaCommand);
            result = obj == null ? 0 : int.Parse(obj.ToString());

            return result;
        }
    }
}
